﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRestoreDRP]
#-- Purpose:		Calls spRestoreDatabase_PointInTime for all databases.
#--	Last Update:	05/21/2012
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	None
#-- Readings:		http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRestoreDRP]
(
	@stopat_time		datetime			= NULL,
	@are_you_sure		bit					= 0
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@db_name sysname

IF ISNULL(@are_you_sure, 0) <> 1
  BEGIN
	RAISERROR('@are_you_sure is mandatory in order to proceed', 0, 1) WITH LOG
	RETURN -9999
  END

--- Set a default value of Now() for @stopat_time if not passed in
IF @stopat_time IS NULL
	SET	@stopat_time = GETDATE()

--- Execute the command
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
	SELECT		bkp.database_name
	FROM		(
				SELECT		bkp.database_name, 
							MAX(bkp.backup_start_date) backup_start_date
				FROM		msdb.dbo.backupset bkp
				JOIN		msdb.dbo.backupmediafamily bmf ON bkp.media_set_id = bmf.media_set_id
							AND dbo.FileExists(physical_device_name) = 1
				WHERE		bkp.database_name NOT IN (SELECT database_name FROM dbo.vwDatabase_DoNotBackup)
							AND bkp.database_name NOT IN ('master', 'model', 'msdb', 'tempdb', DB_NAME())
							AND bkp.type = 'D'
							AND bkp.server_name = @@SERVERNAME			--- Only restore backups that were taken on this server name
				GROUP BY	bkp.database_name
				) t1
	JOIN		msdb.dbo.backupset bkp ON t1.database_name = bkp.database_name
				AND t1.backup_start_date = bkp.backup_start_date
				AND bkp.type = 'D'
	ORDER BY	bkp.backup_size
OPEN crs
FETCH NEXT FROM crs INTO @db_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	BEGIN TRY
		EXEC dbo.spRestoreDatabase_PointInTime	@database_name = @db_name, @stopat_time = @stopat_time, 
												@restore_now = 1, @verify_only = 0
	END TRY
	BEGIN CATCH
		PRINT 'ERROR: An error occurred while restoring ' + @db_name
	END CATCH

	FETCH NEXT FROM crs INTO @db_name
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF
